我们经常把某个字段直接存成json字符串格式,比如tags,extendJson,因为我们根本就不知道用户要填啥,但后面还是需要查,比如查询订单tag里包含退款的业务员
mysql 5.7+
单箭头语法 -> 解析字符串转为json,不去除转义相关符号同JSON_EXTRACT
双箭头请求 ->> 解析字符串为json,同时去除转义符,同JSON_UNQUOTE
JSON_VALID,可以判断一个字符串是否是一个json,为避免报错,可以在查询时加上此条件

测试表test

  • 有如下字段
  • id
  • name
  • jsonObj
  • jsonArray

json数组

  • 数组路径表达式 $[0],$[1]...这样,$[* ] 表示所有成员,没有没示任意一个成员的通配符

简单包含

  • 查询jsonArray字段里包含 "all" 的
select * from test where JSON_CONTAINS(jsonArray->"$[*]",JSON_ARRAY("all"))
-- 也可以这样查,语义没那么明明确,神奇
select * from test where JSON_CONTAINS(jsonArray,CONCAT('"',"all",'"'));
  • 查询果jsonArray里同时含'all' 和 ‘aaa‘的
select * from test where JSON_CONTAINS(jsonArray->"$[*]",JSON_ARRAY("all","aaa"))
  • 查询果jsonArray里同时含'all' 或 ‘aaa‘的
select * from test where JSON_CONTAINS(jsonArray->"$[*]",JSON_ARRAY("all"))
or JSON_CONTAINS(jsonArray->"$[*]",JSON_ARRAY("aaa"))

-- only(mysql 8.0)
select * from test where JSON_OVERLAPS(jsonArray->"$[*]",JSON_ARRAY("all","aaa"))

数组聚合

  • 将所有的name聚合成一个json数组
select  JSON_ARRAYAGG(name)    from  test;
-- 5.7不支持DISTINCT加在字段前页,可以考虑嵌套
select  JSON_ARRAYAGG(name)    from (select DISTINCT name from test) test1;
  • JSON_ARRAYAGG,支持group by分组
select  JSON_ARRAYAGG(name)    from  test group by id;

JSON对象